package pCloud.service;

import java.net.InetSocketAddress;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import pCloud.Utilities;
import pCloud.message.DatabaseReply;
import pCloud.message.DatabaseRequest;
import pCloud.message.DatabaseSlidingWindowRequest;
import pCloud.server.DatabaseHandle;

public class SQLDatabaseHandle implements DatabaseHandle {

	String databaseId, username, password, driver;

	Connection connection;

	/*
	 * Initialize the connection to database.
	 * 
	 * @see pCloud.server.DatabaseHandle#init()
	 */
	@Override
	public void init(String driver, String id, String username, String password)
			throws InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		this.driver = driver;
		this.databaseId = id;
		this.username = username;
		this.password = password;
		Class.forName(driver).newInstance();
		if (username.equals("anonymous"))
			this.connection = DriverManager.getConnection(this.databaseId);
		this.connection = DriverManager.getConnection(this.databaseId,
				username, password);
	}

	public void init(Connection connection) {
		this.connection = connection;
	}

	@Override
	public DatabaseReply evaluateRequest(String joiningColumn,
			DatabaseRequest request) throws Exception {
		String query = request.getQuery();
		Statement stmt = this.connection.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		return constructReplyMessage(joiningColumn, rs);
	}

	public DatabaseReply evaluateRepeatedRequestWithPreparedStm(
			String joiningColumn, PreparedStatement pst, String date1,
			String date2) throws Exception {
		pst.setString(1, date1);
		pst.setString(2, date2);
		long t = System.currentTimeMillis();
		ResultSet rs = pst.executeQuery();
		//System.out.println("Query "+pst+" executed in "+(System.currentTimeMillis()-t)*1.0/1000);
		return constructReplyMessage(joiningColumn, rs);
	}

	public DatabaseReply evaluateSlidingWindowRequest(String joiningColumn,
			DatabaseSlidingWindowRequest request) throws Exception {
		// 1. Construct the select clause;
		String selectClause = "select ";
		String function = request.aggregationFunction;
		int i = 0;
		for (i = 0; i < request.columns.size() - 1; i++)
			selectClause += function + "(" + request.columns.get(i) + "), ";
		selectClause += function + "(" + request.columns.get(i) + ")";

		String whereClause = "";
		Calendar startingPoint = Calendar.getInstance();
		Calendar startingDateTime = Calendar.getInstance();
		Calendar endingDateTime = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		startingDateTime.setTime(sdf.parse(request.startDate));
		endingDateTime.setTime(sdf.parse(request.endDate));

		startingPoint.setTime(startingDateTime.getTime());
		startingPoint.add(Calendar.HOUR, request.windowSize);
		DatabaseRequest dbr;
		DatabaseReply result;

		ArrayList<DatabaseReply> results = new ArrayList<DatabaseReply>();
		String timestampColumnId = "samplingtime";
		DatabaseReply windowReply;
		String preparedString = selectClause
				+ " from "+request.tableId+" where samplingtime > ? and samplingtime < ?";
		PreparedStatement pst = this.connection
				.prepareStatement(preparedString);
		while (startingPoint.before(endingDateTime)) {
			whereClause = " where " + timestampColumnId + " > '"
					+ Utilities.parseCalendarOutput(startingDateTime)
					+ "' and " + timestampColumnId + " < '"
					+ Utilities.parseCalendarOutput(startingPoint) + "'";

			// execute and print
			// System.out.println(selectClause + " from "+request.tableId+
			// whereClause);
			dbr = new DatabaseRequest(selectClause + " from " + request.tableId
					+ whereClause);

			// windowReply = this.evaluateRequest(joiningColumn, dbr);
			windowReply = this.evaluateRepeatedRequestWithPreparedStm(
					joiningColumn, pst,
					Utilities.parseCalendarOutput(startingDateTime),
					Utilities.parseCalendarOutput(startingPoint));

			results.add(windowReply);
			// System.out.println(windowReply);

			// ResultSet results = (ResultSet)rc.executeQuery(selectClause +
			// tableId + whereClause);
			// printResultSet(out, results);

			startingDateTime.add(Calendar.HOUR, request.advanceStep);
			startingPoint.setTime(startingDateTime.getTime());
			startingPoint.add(Calendar.HOUR, request.windowSize);
		}
		if (results.size() == 0)
			return null;
		else
			return this.constructDatabaseReplyFromList(joiningColumn, results);
	}

	/**
	 * When sliding window obligation is used, more than one database are
	 * returned, one for each window. This method merge them into one reply
	 * message
	 * 
	 * We assume that each response contains only one row
	 */
	private DatabaseReply constructDatabaseReplyFromList(
			String joiningColumnId, ArrayList<DatabaseReply> replies) {
		DatabaseReply firstReply = replies.get(0);
		DatabaseReply otherReply;
		Object[] keySet;
		for (int i = 1; i < replies.size(); i++) {
			otherReply = replies.get(i);
			keySet = otherReply.getRowsOfValues().keySet().toArray();
			for (int j = 0; j < keySet.length; j++) {
				firstReply.addKeyValue(i);
				firstReply.addSetOfValuesToRow(i, otherReply.getRowsOfValues()
						.get(keySet[j]));
			}

		}
		return firstReply;
	}

	/**
	 * Going through the result set and fill in the reply with all the result
	 * data.
	 * 
	 * This makes sure that the reply is indexed by the joining column. Each of
	 * that value is mapped to an array of other values that are in the same
	 * order as the columnId array.
	 */
	public DatabaseReply constructReplyMessage(String joiningColumnId,
			ResultSet rs) throws Exception {

		// If no joiningColumnId specified, use the first column as the key.
		// Note this will not result in data being joined at the proxy, since
		// the original joiningColumnId is still null and the proxy will not
		// perform joining.
		DatabaseReply reply = new DatabaseReply(joiningColumnId);

		int columnCount = rs.getMetaData().getColumnCount();
		String[] columnLabels = new String[columnCount];

		ArrayList[] values = new ArrayList[columnCount];

		String columnId;
		int joiningColIndex = -1;

		for (int i = 0; i < columnCount; i++) {
			columnId = rs.getMetaData().getColumnName(i + 1);
			// System.out.println("Column ID = "+columnId);
			if (joiningColumnId == null
					|| !(columnId.toLowerCase().equals(joiningColumnId
							.toLowerCase()))) {
				// System.out.println("Adding column ID "+columnId+" , joiningColumnId = "+joiningColumnId);
				reply.addColumnId(columnId);
			} else
				joiningColIndex = i + 1;
		}

		Object keyValue;
		// System.out.println("******************");
		int counter = 0;
		while (rs.next()) {
			// first, add the value of the joining column
			if (joiningColumnId != null && joiningColIndex != -1)
				keyValue = rs.getObject(joiningColIndex);
			else
				keyValue = counter;
			counter++;
			reply.addKeyValue(keyValue);
			// System.out.print("Value = "+keyValue+"\t");
			for (int i = 0; i < columnCount; i++) {
				if ((i + 1) != joiningColIndex) {
					reply.addValueToRow(keyValue, rs.getObject(i + 1));
					// System.out.print(rs.getObject(i+1)+"\t");
				}
			}
			// System.out.println();
		}
		return reply;
	}

	public DatabaseReply constructShowTableReplyMessage() throws Exception {
		Statement st = this.connection.createStatement();
		ResultSet rs = st.executeQuery("show tables");
		DatabaseReply reply = new DatabaseReply(null);

		String columnLabels = "table_name";

		reply.addColumnId(columnLabels);

		Object keyValue;
		// System.out.println("******************");
		int counter = 0;
		String tableName;
		while (rs.next()) {
			// first, add the value of the joining column
			keyValue = counter;
			counter++;
			reply.addKeyValue(keyValue);
			tableName = rs.getString(1);
			reply.addValueToRow(keyValue, tableName);
		}
		return reply;
	}

	private DatabaseReply constructShowTableReplyPostgesql() throws Exception {
		DatabaseMetaData dmd = this.connection.getMetaData();
		ResultSet rs = dmd.getTables(null, "public", "%", null);

		DatabaseReply reply = new DatabaseReply(null);

		String columnLabels = "table_name";

		ArrayList values = new ArrayList();

		reply.addColumnId(columnLabels);

		Object keyValue;
		// System.out.println("******************");
		int counter = 0;
		String tableName;
		while (rs.next()) {
			// first, add the value of the joining column
			keyValue = counter;
			counter++;
			reply.addKeyValue(keyValue);
			tableName = rs.getString(3);
			reply.addValueToRow(keyValue, tableName);
		}
		return reply;
	}

	public DatabaseReply constructShowColumnReplyMessage(String tableName)
			throws Exception {
		Statement st = this.connection.createStatement();

		System.out.println("Executing query:  describe " + tableName);
		ResultSet rs = st.executeQuery("describe " + tableName);

		DatabaseReply reply = new DatabaseReply(null);

		String columnLabels = "column_name";

		reply.addColumnId(columnLabels);

		Object keyValue;
		// System.out.println("******************");
		int counter = 0;
		while (rs.next()) {
			// first, add the value of the joining column
			keyValue = counter;
			counter++;
			reply.addKeyValue(keyValue);
			reply.addValueToRow(keyValue,
					rs.getString(1) + "\t" + rs.getString(2));
		}
		return reply;
	}

	@Override
	public Connection getConnection() {
		return this.connection;
	}

	@Override
	public Object eavaluateRequest(String query) throws Exception {
		Statement stmt = this.connection.createStatement();
		// ResultSet rs = this.connection.getMetaData().getTables(null, null,
		// "%", null);
		// while (rs.next())
		// System.out.println(rs.getString(3));

		ResultSet rs = stmt.executeQuery("'\'d");

		return rs;
	}

}
